﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class StudentDAO : CommonDAO
    {
        private string sql_getStudentByNumAndPwd = "select StudentID from Students where StudentID=@StudentID and StudentPwd=@StudentPwd";
        private string table_student = "Students";
        private string sql_getStudentPlanByStudentID = "select FirstWillTeacher,SecondWillTeacher,ThirdWillTeacher from StudentChooseTutorInfo where StudentID=@StudentID";


        public StudentDAO()
            : base()
        {

        }

        // @StudentID
        // @StudentPwd
        public DataSet getStudentByStudentNumPwd(string strStudentNum, string strPwd)
        {
            SqlCommand cmd = this.getCommand(sql_getStudentByNumAndPwd);
            cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStudentNum;
            cmd.Parameters.Add("@StudentPwd", SqlDbType.NChar, 12).Value = strPwd;
            return this.executeCommand(cmd, table_student);
        }

        // @StudentID
        public DataSet getStudentPlanByStudentID(string strStudentNum)
        {
            SqlCommand cmd = this.getCommand(sql_getStudentPlanByStudentID);
            cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStudentNum;
            return this.executeCommand(cmd, table_student);
        }


        // The order should be changed when the application is deleted.
        // 2 the applied teacher's ID 
        // 3 the application rank
        public bool cancelPlan(string strStuID, string strTeacherID, int iApplyNum, int iCancelNum)
        {
            // the parameters are iligal
            if (iApplyNum < 0 || iApplyNum > 2) return false;
            if (iCancelNum > iApplyNum + 1 || iCancelNum < 0) return false;

            DataSet dsStudentPlan = this.getStudentPlanByStudentID(strStuID);
            DataRow rowPlan = dsStudentPlan.Tables[0].Rows[0];
            string strFirstWillTeacher = rowPlan[0].ToString().Trim();
            string strSecondWillTeacher = rowPlan[1].ToString().Trim();
            string strThirdWillTeacher = rowPlan[2].ToString().Trim();

            // Only the first plan is canceled
            if (iApplyNum == 0)
            {

                string sql_updateFirstWillTeacher = "UPDATE StudentChooseTutorInfo SET [FirstWillTeacher] = null, [SecondWillTeacher] = null, [ThirdWillTeacher] = null WHERE [StudentID] = @StudentID";
                SqlCommand cmd = this.getCommand(sql_updateFirstWillTeacher);
                cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                int iRet = this.ExecuteNonQueryCommand(cmd);
                if (iRet != 1)
                    return false;
            }
            else if (iApplyNum == 1)
            {
                // If it's the secondplan being canceled
                if (iCancelNum == 2)
                {
                    string sql_updateSecondWillTeacher = "UPDATE StudentChooseTutorInfo SET [SecondWillTeacher] = null WHERE [StudentID] = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateSecondWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }

                if (iCancelNum == 1)
                {
                    string sql_updateSecondWillTeacher = "UPDATE StudentChooseTutorInfo SET [FirstWillTeacher] = (select SecondWillTeacher from Students where StudentID = @StudentID),[SecondWillTeacher] = null WHERE StudentID = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateSecondWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }

            }
            else if (iApplyNum == 2)
            {
                // update the third plan
                if (iCancelNum == 3)
                {
                    string sql_updateThirdWillTeacher = "UPDATE StudentChooseTutorInfo SET [ThirdWillTeacher] = null WHERE [StudentID] = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateThirdWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }
                // Update the second plan
                else if (iCancelNum == 2)
                {
                    string sql_updateSecondWillTeacher = "UPDATE StudentChooseTutorInfo SET [SecondWillTeacher]=(select ThirdWillTeacher from Students where StudentID=@StudentID),[ThirdWillTeacher] = null WHERE [StudentID] = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateSecondWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }
                // update the firstplan
                else if (iCancelNum == 1)
                {
                    string sql_updateFirstWillTeacher = "UPDATE StudentChooseTutorInfo SET [FirstWillTeacher] = (select SecondWillTeacher From Students where StudentID = @StudentID), [SecondWillTeacher] = (select ThirdWillTeacher from Student where StudentID = @StudentID), [ThirdWillTeacher] = null WHERE [StudentID] = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateFirstWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }
            }
            TeacherPlanDAO teacherPlanDao = new TeacherPlanDAO();
            teacherPlanDao.eraseStudent(strTeacherID);
            return true;
        }

        // Gurrentee that the FirstWillTeacher is taken first and then the SecondWillTeacher
        public bool applyForTeacher(string strStuID, string strTeacherID, int iApplyNum)
        {
            TeacherPlanDAO teacherPlanDao = new TeacherPlanDAO();
            // If the teacher has some empty seat
            if (!teacherPlanDao.isTeacherFull(strTeacherID))
            {
                if (iApplyNum < 1 || iApplyNum > 3) return false;

                if (iApplyNum == 1)
                {
                    string sql_updateFirstWillTeacher = "UPDATE StudentChooseTutorInfo SET [FirstWillTeacher] = @FirstWillTeacher WHERE [StudentID] = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateFirstWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    cmd.Parameters.Add("@FirstWillTeacher", SqlDbType.NChar, 10).Value = strTeacherID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }
                else if (iApplyNum == 2)
                {
                    string sql_updateSecondWillTeacher = "UPDATE StudentChooseTutorInfo SET [SecondWillTeacher] = @SecondWillTeacher WHERE [StudentID] = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateSecondWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    cmd.Parameters.Add("@SecondWillTeacher", SqlDbType.NChar, 10).Value = strTeacherID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }
                else if (iApplyNum == 3)
                {
                    string sql_updateSecondWillTeacher = "UPDATE StudentChooseTutorInfo SET [ThirdWillTeacher] = @ThirdWillTeacher WHERE [StudentID] = @StudentID";
                    SqlCommand cmd = this.getCommand(sql_updateSecondWillTeacher);
                    cmd.Parameters.Add("@StudentID", SqlDbType.NChar, 10).Value = strStuID;
                    cmd.Parameters.Add("@ThirdWillTeacher", SqlDbType.NChar, 10).Value = strTeacherID;
                    int iRet = this.ExecuteNonQueryCommand(cmd);
                    if (iRet != 1)
                        return false;
                }
                teacherPlanDao.addStudent(strTeacherID);
                return true;
            }

            else
            {
                return false;
            }
        }
    }
}
